/**
 * 
 */
package com.easidea.tim.service.finance;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springside.core.service.EntityService;

import com.easidea.tim.dao.customer.CustomerDao;
import com.easidea.tim.dao.finance.CustomerAccountListingDao;
import com.easidea.tim.dao.finance.FinanceItemDao;
import com.easidea.tim.dto.report.FinanceAccountAgeReportDTO;
import com.easidea.tim.entity.customer.Customer;
import com.easidea.tim.entity.finance.CustomerAccountListing;
import com.easidea.tim.entity.finance.FinanceItem;
import com.easidea.tim.util.Page;

/**
 * @author Harrison
 * 
 */
public class CustomerAccountListingService extends
		EntityService<CustomerAccountListing, CustomerAccountListingDao> {

	private CustomerAccountListingDao customerAccountListingDao;
	private CustomerDao customerDao;
	private FinanceItemDao financeItemDao;

	public Page getAccoutListing(Long customerId, Date startDate, Date endDate,
			Page page) {
		if (customerId != null && startDate != null && endDate != null) {
			Calendar calendar = GregorianCalendar.getInstance();
			calendar.setTime(endDate);
			calendar.add(Calendar.DATE, 1); // 替换到指定日期的下一日，因为带入计算的是"<"
			endDate = calendar.getTime();
			
			Criteria countCte = genCte(customerId, startDate, endDate);
			countCte.setProjection(Projections.count("id"));
			Integer totalCounts = (Integer) countCte.uniqueResult();
			countCte = null;

			Criteria resultCte = genCte(customerId, startDate, endDate);
			int startIndex = Page.getStartOfPage(page.getCurrentPageNo(), page
					.getPageSize());
			resultCte.setFirstResult(startIndex);
			resultCte.setMaxResults(page.getPageSize());
			List<CustomerAccountListing> list = (List<CustomerAccountListing>) resultCte
					.list();
			resultCte = null;
			Page resultPage = new Page(startIndex, totalCounts, page
					.getPageSize(), list);
			resultPage.setCurrentPageNo(page.getCurrentPageNo()); // 分页数据

			return resultPage;
		}
		return null;
	}

	private Criteria genCte(Long customerId, Date startDate, Date endDate) {
		Criteria cte = customerAccountListingDao.createUnDeletedCriteria();
		cte.add(Restrictions.eq("customer.id", customerId));
		cte.add(Restrictions.ge("createTime", startDate));
//		cte.add(Restrictions.lt("createTime", getNextDay(endDate)));
		cte.add(Restrictions.lt("createTime", endDate));
		cte.createAlias("item", "item");
		//cte.add(Restrictions.ne("item.direction", (short)999));
		return cte;
	}
	
	private Date getNextDay(Date date){
		Calendar calendar = new GregorianCalendar();
		calendar.setTime(date);
		calendar.add(Calendar.DAY_OF_MONTH, 1);
		return calendar.getTime();
	}

	/**
	 * 根据客户名称查询记录
	 * 
	 * @param name
	 * @param year
	 * @param month
	 * @param page
	 * @return
	 */
	public Page getCustomerAccountListingArray(String name, Integer year,
			Integer month, Page page) {
		if (year != null && month != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.set(Calendar.YEAR, year);
			calendar.set(Calendar.MONTH, month - 1);
			calendar.set(Calendar.DATE, 1);
			calendar.set(Calendar.HOUR,0);
			calendar.set(Calendar.MINUTE,0);
			calendar.set(Calendar.SECOND, 0);
			calendar.set(Calendar.MILLISECOND, 0);
			Date startDate = calendar.getTime(); // 月初
			
			calendar.add(Calendar.MONTH, 1); // 调整为下月的1日，用于参与比较时用 "<" 进行计算
			Date endDate = calendar.getTime(); // 月末

			Criteria countCte = genCte(name, startDate, endDate);
			countCte.setProjection(Projections.count("id"));
			Integer totalCounts = (Integer) countCte.uniqueResult();

			int startIndex = Page.getStartOfPage(page.getCurrentPageNo(), page
					.getPageSize());
			
			StringBuffer sqlBF = genListCALOnlySQL(name, null, startDate, endDate, startIndex, page.getPageSize(), false); // 获得对应的sql
			List<CustomerAccountListing> ls = listCalOnlyBySQL(sqlBF.toString()); // 获得数据

			Page resultPage = new Page(startIndex, totalCounts, page
					.getPageSize(), ls);
			resultPage.setCurrentPageNo(page.getCurrentPageNo());

			return resultPage;
		}
		return null;
	}

	private Criteria genCte(String name, Date startDate, Date endDate) {
		Criteria cte = customerAccountListingDao.createUnDeletedCriteria();
		cte.add(Restrictions.ge("createTime", startDate));
		cte.add(Restrictions.lt("createTime", endDate));
		
		FinanceItem salesFinancItem = financeItemDao.getSalesAccountItem();
		if(salesFinancItem != null){
			cte.add(Restrictions.ne("item.id", salesFinancItem.getId()));
		}
		
		FinanceItem salesReturnFi = financeItemDao.getSalesReturnAccountItem();
		if(salesReturnFi != null){
			cte.add(Restrictions.ne("item.id", salesReturnFi.getId()));
		}
		
		if (StringUtils.isNotEmpty(name)) {
			cte.createAlias("customer", "customer");
			cte.add(Restrictions.ilike("customer.name", "%"+name+"%"));
		}
		return cte;
	}
	
	/**
	 * 
	 * @param name
	 * @param customerId
	 * @param startDate
	 * @param endDate 不包含该日期
	 * @param startIndex
	 * @param pageSize
	 * @param isViewFItemSales 是否显示销售结算的科目数据
	 * @return
	 */
	private StringBuffer genListCALOnlySQL(String name, Long customerId, Date startDate,
			Date endDate, Integer startIndex, Integer pageSize, boolean isViewFItemSales) {
		StringBuffer hqlBF =  new StringBuffer("select cal.id, cal.creator, cal.create_time, cal.deleted, cal.modifier, cal.modified_time, " +
				"cal.total_amount, " +
				// " CASE it.direction WHEN -1 THEN cal.amount*-1 ELSE cal.amount END, " + ---- 已经修正保存收支信息中的amount正负数
				" cal.amount, " +
				" cal.memo, cal.last_amount, cal.item_name, cal.item_id, cal.customer_name, cal.customer_id " +
				"from tim_finance_customer_account_listing cal, tim_customer_customer cust " +
				"where cal.customer_id = cust.id and cal.deleted = 0 and cust.deleted = 0");
		if(!isViewFItemSales){
			hqlBF.append(" and cal.item_id <> " + financeItemDao.getSalesAccountItem().getId() 
					+ "  and cal.item_id <> " + financeItemDao.getSalesReturnAccountItem().getId());
		}
		if(StringUtils.isNotEmpty(name) && customerId == null){
			hqlBF.append(" and cust.name like '%"+ name +"%' ");
		}
		if(customerId != null){
			hqlBF.append(" and cust.id = " + customerId);
		}
		
		if(startDate != null && endDate != null && startDate.before(endDate)){
			SimpleDateFormat df =new SimpleDateFormat("yyyy-MM-dd");
			hqlBF.append(" and "  +"cal.create_time >= '" + df.format(startDate) +
					"' and cal.create_time < '" + df.format(endDate) + "'");
			hqlBF.append(" ORDER BY cal.create_time ASC ");
		}
		if(startIndex != null && startIndex.intValue()>=0){
			hqlBF.append(" limit " +startIndex.intValue());
		}
		if(pageSize != null){
			if(hqlBF.indexOf("limit") > 0 ) {
				hqlBF.append(" ,"+pageSize.intValue());
			}else{
				hqlBF.append(" limit " +pageSize.intValue());
			}
		}
		return hqlBF;
	}

	private List<CustomerAccountListing> listCalOnlyBySQL(final String sql) {
		return (List<CustomerAccountListing>)customerAccountListingDao.getHibernateTemplate().execute(new HibernateCallback(){

			/* (non-Javadoc)
			 * @see org.springframework.orm.hibernate3.HibernateCallback#doInHibernate(org.hibernate.Session)
			 */
			public List<CustomerAccountListing> doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery query = session.createSQLQuery(sql);
				List<Object[]> result = (List<Object[]>)query.list();
				if(result != null && result.size()>0){
					List<CustomerAccountListing> calList = new ArrayList<CustomerAccountListing>();
					for(Object[] calObj:result){
						CustomerAccountListing cal = new CustomerAccountListing();
						if(calObj[0] != null) cal.setId(((BigInteger)calObj[0]).longValue());
						if(calObj[1] != null) cal.setCreator((String)calObj[1]);
						if(calObj[2] != null) cal.setCreateTime((Date)calObj[2]);
						if(calObj[3] != null) cal.setDeleted((Short)calObj[3]);
						if(calObj[4] != null) cal.setModifier((String)calObj[4]);
						if(calObj[5] != null) cal.setModifyTime((Date)calObj[5]);
						if(calObj[6] != null) cal.setTotalAmount(((BigDecimal)calObj[6]).doubleValue());
						if(calObj[7] != null) cal.setAmount(((BigDecimal)calObj[7]).doubleValue());
						if(calObj[8] != null) cal.setMemo((String)calObj[8]);
						if(calObj[9] != null) cal.setLastAmount(((BigDecimal)calObj[9]).doubleValue());
						if(calObj[10] != null) cal.setItemName((String)calObj[10]);
						if(calObj[11] != null) {
							FinanceItem it = new FinanceItem();
							it.setId(((BigInteger)calObj[11]).longValue());
							cal.setItem(it);
						}
						if(calObj[12] != null)cal.setCustomerName((String)calObj[12]);
						if(calObj[13] != null){
							Customer cust = new Customer();
							//cust.setId(((BigInteger)calObj[13]).longValue());
							cust = customerDao.get(((BigInteger)calObj[13]).longValue());
							cal.setCustomer(cust);
							cal.setCreator(cust.getStaff().getName());
						}
					
						calList.add(cal);
					}
					return calList;
				}else
					return null;
			}
		});
	}
	
	/**
	 * 根据客户名称、月份查询客户收支信息
	 * 
	 * @param customerName
	 * @param year
	 * @param month
	 * @return
	 */
	public List<CustomerAccountListing> getCustomerAccountListingArray(
			String customerName, int year, int month) {
		Calendar calendar = Calendar.getInstance();
		calendar.set(Calendar.YEAR, year);
		calendar.set(Calendar.MONTH, month - 1);
		calendar.set(Calendar.DATE, 1);
		calendar.set(Calendar.HOUR,0);
		calendar.set(Calendar.MINUTE,0);
		calendar.set(Calendar.SECOND, 0);
		calendar.set(Calendar.MILLISECOND, 0);
		Date startDate = calendar.getTime(); // 月初

		calendar.add(Calendar.MONTH, 1); // 调整为下月的1日，用于参与比较时用 "<" 进行计算
		Date endDate = calendar.getTime(); // 月末

		StringBuffer sqlBF = genListCALOnlySQL(customerName,null, startDate, endDate, null, null, false); // 获得对应的sql
		List<CustomerAccountListing> ls = listCalOnlyBySQL(sqlBF.toString()); // 获得数据
		return ls;
}
	
	/**
	 * 根据客户Id、开始日期、结束日期提取对账信息
	 * 
	 * @param customerId
	 * @param startDate
	 * @param endDate 不包括endDate
	 * @return
	 */
	public List<CustomerAccountListing> getCustomerAccountListingArray(Long customerId, Date startDate, Date endDate){

			StringBuffer sqlBF = genListCALOnlySQL(null, customerId, startDate, endDate, null, null, true); // 获得对应的sql
			List<CustomerAccountListing> ls = listCalOnlyBySQL(sqlBF.toString()); // 获得数据
			return ls;
	}
	
	public boolean addCustomerAccoutListing(CustomerAccountListing cal) {
		return customerAccountListingDao.addCustomerAccoutListing(cal);
	}

	/**
	 * 
	 * 获取账龄报表
	 * @return
	 */
	public List<FinanceAccountAgeReportDTO> accountAgeReport(){
//		final String sql = "SELECT c.name as cName, s.name as sName, l.customer_id as cId, c.account_summary as aSumm,"
//			+ " SUM(CASE TO_DAYS(NOW())-TO_DAYS(l.create_time) <= 30 WHEN TRUE THEN l.amount END) AS '<30',"
//			+ " SUM(CASE 60 >= TO_DAYS(NOW())-TO_DAYS(l.create_time) > 30 WHEN TRUE THEN l.amount END) AS '<60',"
//			+ " SUM(CASE 90 >= TO_DAYS(NOW())-TO_DAYS(l.create_time) > 60 WHEN TRUE THEN l.amount END) AS '<90',"
//			+ " SUM(CASE TO_DAYS(NOW())-TO_DAYS(l.create_time) >= 91 WHEN TRUE THEN l.amount END) AS '>91',"
//			+ " c.account_memo as aMemo"
//			+ " FROM tim.tim_finance_customer_account_listing l, tim.tim_customer_customer c, tim.tim_orgnization_staff s"
//			+ " WHERE c.id = l.customer_id AND c.staff_id = s.id AND l.deleted = 0 " 
//			+ " GROUP BY l.customer_id";
		
		final String sql = "SELECT cust.name AS cName ,st.name AS sName, cust.id AS cId, cust.account_summary AS aSumm, 0 AS '<30',0 AS '<60', 0 AS '<90', cust.account_summary AS '>91', cust.account_memo AS aMemo " +
				" FROM tim_customer_customer cust, tim_orgnization_staff st WHERE st.id = cust.staff_id AND cust.deleted = 0 AND cust.id NOT IN( " +
				" SELECT DISTINCT cl.customer_id FROM tim_finance_customer_account_listing cl WHERE cl.deleted = 0 AND cl.customer_id IS NOT NULL)" +
				" UNION (" +
				" SELECT c.name AS cName, s.name AS sName, l.customer_id AS cId, c.account_summary AS aSumm," +
				" IFNULL(SUM(CASE TO_DAYS(NOW())-TO_DAYS(l.create_time) <= 30 WHEN TRUE THEN l.amount END),0) AS '<30'," +
				" IFNULL(SUM(CASE TO_DAYS(NOW())-TO_DAYS(l.create_time) > 30 AND TO_DAYS(NOW())-TO_DAYS(l.create_time) <= 60 WHEN TRUE THEN l.amount END),0) AS '<60'," +
				" IFNULL(SUM(CASE TO_DAYS(NOW())-TO_DAYS(l.create_time) > 60 AND TO_DAYS(NOW())-TO_DAYS(l.create_time) <= 90 WHEN TRUE THEN l.amount END),0) AS '<90'," +
				" IF(c.account_summary = (" +
				"	IFNULL(SUM(CASE TO_DAYS(NOW())-TO_DAYS(l.create_time) <= 30 WHEN TRUE THEN l.amount END),0)" +
				"	+IFNULL(SUM(CASE TO_DAYS(NOW())-TO_DAYS(l.create_time) > 30 AND TO_DAYS(NOW())-TO_DAYS(l.create_time) <= 60 WHEN TRUE THEN l.amount END),0)" +
				"	+IFNULL(SUM(CASE TO_DAYS(NOW())-TO_DAYS(l.create_time) > 60 AND TO_DAYS(NOW())-TO_DAYS(l.create_time) <= 90 WHEN TRUE THEN l.amount END),0)" +
				"	),0, c.account_summary - (" +
				"	IFNULL(SUM(CASE TO_DAYS(NOW())-TO_DAYS(l.create_time) <= 30 WHEN TRUE THEN l.amount END),0)" +
				"	+IFNULL(SUM(CASE TO_DAYS(NOW())-TO_DAYS(l.create_time) > 30 AND TO_DAYS(NOW())-TO_DAYS(l.create_time) <= 60 WHEN TRUE THEN l.amount END),0)" +
				"	+IFNULL(SUM(CASE TO_DAYS(NOW())-TO_DAYS(l.create_time) > 60 AND TO_DAYS(NOW())-TO_DAYS(l.create_time) <= 90 WHEN TRUE THEN l.amount END),0)" +
				"	)) AS '>91',  c.account_memo AS aMemo" +
				"  FROM tim.tim_finance_customer_account_listing l ,tim.tim_customer_customer c ,tim.tim_orgnization_staff s" +
				"  WHERE	c.id = l.customer_id  AND c.staff_id = s.id AND l.deleted = 0 AND c.deleted = 0" +
				"  GROUP BY l.customer_id)";
		
		return (List<FinanceAccountAgeReportDTO>) customerAccountListingDao.getHibernateTemplate().execute(new HibernateCallback() {
			 public Object doInHibernate(Session session) throws HibernateException, SQLException {
				  SQLQuery query = session.createSQLQuery(sql);
				  query.addScalar("CNAME");
				  query.addScalar("SNAME");
				  query.addScalar("CID");
				  query.addScalar("ASUMM");
				  query.addScalar("<30");
				  query.addScalar("<60");
				  query.addScalar("<90");
				  query.addScalar(">91");
				  query.addScalar("AMEMO");
				  List<Object[]> rst = query.list();
				  List<FinanceAccountAgeReportDTO> list = new ArrayList<FinanceAccountAgeReportDTO>();
				  for(Object[] obj :rst){
					  FinanceAccountAgeReportDTO dto = new FinanceAccountAgeReportDTO();
					  if(obj[0] != null)dto.setCustomerName((String)obj[0]);
					  if(obj[1] != null)dto.setStaffName((String)obj[1]);
					  if(obj[2] != null)dto.setCustomerId(((BigInteger)obj[2]).longValue());
					  if(obj[3] != null)
						  dto.setAccountSummary(((BigDecimal)obj[3]).doubleValue());
					  else
						  dto.setAccountSummary(0d);
					  if(obj[4] != null)
						  dto.setLessThan30(((BigDecimal)obj[4]).doubleValue());
					  else 
						  dto.setLessThan30(0d);
					  if(obj[5] != null)
						  dto.setLessThan60(((BigDecimal)obj[5]).doubleValue());
					  else
						  dto.setLessThan60(0d);
					  if(obj[6] != null)
						  dto.setLessThan90(((BigDecimal)obj[6]).doubleValue());
					  else
						  dto.setLessThan90(0d);
					  if(obj[7] != null)
						  dto.setGreatThan91(((BigDecimal)obj[7]).doubleValue());
					  else
						  dto.setGreatThan91(0d);
					  if(obj[8] != null)dto.setAccountMemo((String)obj[8]);
					  
					  list.add(dto);
				  }
				  
				  return list;
			 }
		});
	}
	
	/*
	 * (non-Javadoc)
	 * 
	 * @see org.springside.core.service.EntityService#getEntityDao()
	 */
	@Override
	public CustomerAccountListingDao getEntityDao() {
		return customerAccountListingDao;
	}

	/**
	 * @return the customerAccountListingDao
	 */
	public CustomerAccountListingDao getCustomerAccountListingDao() {
		return customerAccountListingDao;
	}

	/**
	 * @param customerAccountListingDao
	 *            the customerAccountListingDao to set
	 */
	public void setCustomerAccountListingDao(
			CustomerAccountListingDao customerAccountListingDao) {
		this.customerAccountListingDao = customerAccountListingDao;
	}

	/**
	 * @return the customerDao
	 */
	public CustomerDao getCustomerDao() {
		return customerDao;
	}

	/**
	 * @param customerDao
	 *            the customerDao to set
	 */
	public void setCustomerDao(CustomerDao customerDao) {
		this.customerDao = customerDao;
	}

	/**
	 * @return the financeItemDao
	 */
	public FinanceItemDao getFinanceItemDao() {
		return financeItemDao;
	}

	/**
	 * @param financeItemDao
	 *            the financeItemDao to set
	 */
	public void setFinanceItemDao(FinanceItemDao financeItemDao) {
		this.financeItemDao = financeItemDao;
	}

	

}
